
ALTER             function  fn_get_valid_wafers_count ( @assembly_release_id int, @lot_id int )
 returns  @@fn_results Table ( wafer_id varchar(50), bin_total bigint )
 as
 begin
 
 declare @mainCursor 	CURSOR
 declare @subCursor	CURSOR
 declare @wafer_id 	varchar(50)
 declare @wafer_saved   int
 declare @wafer_id_temp varchar(50)
 declare @bin_total	bigint
 declare @wafer_exists  int
 
 declare @results Table ( wafer_id varchar(50), bin_total bigint )
 
 	if( @assembly_release_id > 0 )
 	begin
 		
 		set @mainCursor = CURSOR FOR select wafer_id, ( bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 )  from informative_binning NOLOCK where lot_id = @lot_id
 		open @mainCursor 
 		fetch next from @mainCursor into @wafer_id, @bin_total 
 		while @@fetch_status = 0
 		begin
 			select @wafer_saved = count( lot_id ) from Assembly_Release_Bin_Information NOLOCK where lot_id = @lot_id and wafer_id = @wafer_id
 			if( @wafer_saved = 0 ) 
 			begin
 				insert into @results values ( @wafer_id, @bin_total ) 
 			end
 		
 		fetch next from @mainCursor into @wafer_id, @bin_total  
 		end
 		close @mainCursor
 		deallocate @mainCursor	
 		
 		set @subCursor = CURSOR for select wafer_id from assembly_release_bin_information where assemblyrelease_id = @assembly_release_id 
 		open @subCursor	fetch next from @subCursor into @wafer_id_temp
 		while @@fetch_status = 0
 		begin
 			select @wafer_exists = count( wafer_id ) from @results where wafer_id = @wafer_id_temp
 			if( @wafer_exists = 0 )		
 			begin
 				insert into @results Select wafer_id, bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 bin_total from informative_binning where lot_id = @lot_id and wafer_id = @wafer_id_temp
 			end
 		fetch next from @subCursor into @wafer_id_temp
 		end
 		close @subCursor
 		deallocate @subCursor
 
 	end
 	else
 	begin
 		set @mainCursor = CURSOR FOR select wafer_id, ( bin_1 + bin_2 + bin_3 + bin_4 + bin_5 + bin_6 + bin_7 + bin_8 + bin_9 + bin_10 + bin_11 + bin_12 + bin_13 + bin_14 + bin_15 )  from informative_binning NOLOCK where lot_id = @lot_id
 		open @mainCursor 
 		fetch next from @mainCursor into @wafer_id, @bin_total 
 		while @@fetch_status = 0
 		begin
 		
 			select @wafer_saved = count( lot_id ) from Assembly_Release_Bin_Information NOLOCK where lot_id = @lot_id and wafer_id = @wafer_id
 			if( @wafer_saved = 0 ) 
 			begin
 				insert into @results values ( @wafer_id, @bin_total ) 
 			end
 		
 		
 		fetch next from @mainCursor into @wafer_id, @bin_total  
 		end
 		close @mainCursor
 		deallocate @mainCursor
 	end
insert @@fn_results select * from @results
return

 end

